/**
 * 
 */
package org.crystal.jqweb.db;

import java.sql.*;
import java.util.*;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.crystal.jqweb.util.BeanUtils;

/**
 * @author Crystal
 *
 */
public class DbUtils {
	private final Log log = LogFactory.getLog(getClass());
	private DataSource dataSource;
	private Connection connection;
	private Statement	statement;
	private ResultSet	resultSet;
	private ResultSetMetaData	resultSetMetaData;
	private PreparedStatement	preparedStatement;
	private String className;
	private String url;
	private String user;
	private String password;
	private int page=1;
	private int pageResults=100;
	private boolean selfConnection=false;
	private boolean scrollable=true;

	/**
	 * 
	 */
	public DbUtils() {
		// TODO Auto-generated constructor stub
	}
	
	/**
	 * 
	 */
	public DbUtils(DataSource dataSource) {
		// TODO Auto-generated constructor stub
		this.dataSource=dataSource;
	}
	
	/**
	 * 
	 */
	public DbUtils(Connection connection) {
		// TODO Auto-generated constructor stub
		this.connection=connection;
	}
	/**
	 * 
	 */
	public DbUtils(String className,String url,String user,String password) {
		// TODO Auto-generated constructor stub
		this.className=className;
		this.url=url;
		this.user=user;
		this.password=password;
	}

	public boolean beginTransaction(){
		log.debug("beginTransaction()!");
		try {
			connection.setAutoCommit(false);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	public boolean commit(){
		try {
			connection.commit();
			connection.setAutoCommit(true);
			log.debug("commit() End Transaction!");
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				connection.rollback();
				connection.setAutoCommit(true);
				log.debug("commit() Rollback Transaction!");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		}
	}

	/**
	 * @param dataSource the dataSource to set
	 */
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * @param connection the connection to set
	 */
	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	/**
	 * @return the connection
	 */
	public Connection getConnection() {
		log.debug("getConnection()");
		if(connection==null){
			if(dataSource!=null){
				try {
					connection=dataSource.getConnection();
					log.debug("getConnection() dataSource success!");
					return connection;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}else if(connection==null){
				try {
					Class.forName(this.className);
					connection = DriverManager.getConnection(this.url, this.user, this.password );
					selfConnection=true;
					log.debug("getConnection() connection success!");
					log.debug("getConnection() selfConnection:"+selfConnection);	
					
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
			}
		}
		return connection;
	}
	
	public boolean execute(String sql){
		try {
			log.info("execute() sql:"+sql);
			statement=getConnection().createStatement();
			statement.execute(sql);
			statement.close();
			statement=null;
			
			if(selfConnection){	
				connection.close();
				connection=null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public boolean executeBatch(String []sql){
		try {
			log.info("executeBatch() sql size:"+sql.length);
			statement=getConnection().createStatement();
			
			for(int i=0;i<sql.length;i++){
				statement.addBatch(sql[i]);
				log.debug("executeBatch() sql["+i+"]:"+sql[i]);
			}
			statement.executeBatch();

			statement.close();
			statement=null;
			
			if(selfConnection){	
				connection.close();
				connection=null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}
	public int executeUpdate(String sql){
		try {
			log.info("executeUpdate() sql:"+sql);
			statement=getConnection().createStatement();
			int row =statement.executeUpdate(sql);
			statement.close();
			statement=null;
			
			if(selfConnection){	
				connection.close();
				connection=null;
			}
			return row;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
	}
	
	@SuppressWarnings("unchecked")
	public <T> List <T> executeQuery(Class cls,String sql){
		List <T> queryList=null;
		log.info("executeQuery() sql:"+sql);
		try {
			if(scrollable){
				preparedStatement=getConnection().prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			}else{
				preparedStatement=getConnection().prepareStatement(sql);
			}
			resultSet =preparedStatement.executeQuery(sql);
			resultSetMetaData = resultSet.getMetaData();
			queryList=new ArrayList<T>();
			
			int rowCounts=0;
			if(scrollable&&this.page!=1){
				resultSet.absolute((this.page-1)*this.pageResults);
			}
			
			while(resultSet.next()){
				if(!scrollable){
					if(rowCounts<(this.page-1)*this.pageResults)continue;
				}
				if(rowCounts++>=this.page*this.pageResults)break;
				
				T entity  =(T)BeanUtils.newInstance((Class)cls);
				Map<String,String> tableValues=new HashMap<String,String>();
				for(int i=1;i<=resultSetMetaData.getColumnCount();i++){
					String dbColumnName=resultSetMetaData.getColumnName(i);
					String value=resultSet.getString(dbColumnName);
					tableValues.put(dbColumnName, value);
				}
				log.debug("executeQuery() tableValues:"+tableValues);
				
				log.debug("executeQuery()"+entity.getClass().getName());
				ORMapping.table2Bean(entity, tableValues);
				try {
					if(log.isDebugEnabled()){
						BeanUtils.displayValues(entity);
					}
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				queryList.add(entity);
			}
			
			resultSet.close();
			preparedStatement.close();
			resultSet=null;
			preparedStatement=null;
			if(selfConnection){
				connection.close();
				connection=null;
			}
			
			log.debug("executeQuery() queryList:"+queryList);
			log.info("executeQuery() queryList Size:"+queryList.size());
			return queryList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	public String getByFunction(String sql){
		
		String queryResult="0";
		try {
			log.info("getByFunction() sql :"+sql);
			statement=getConnection().createStatement();			
			resultSet=statement.executeQuery(sql);
			
			resultSet.next();
			queryResult=resultSet.getString(1);
			
			resultSet.close();
			resultSet=null;
			
			statement.close();
			statement=null;
			
			if(selfConnection){	
				connection.close();
				connection=null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}
		return queryResult;
	}
	/**
	 * @param page the page to set
	 */
	public void setPage(int page) {
		this.page = page;
	}

	/**
	 * @param pageResults the pageResults to set
	 */
	public void setPageResults(int pageResults) {
		this.pageResults = pageResults;
	}
	
	
	/**
	 * @param scrollable the scrollable to set
	 */
	public void setScrollable(boolean scrollable) {
		this.scrollable = scrollable;
	}

}
